import pymysql
import xlrd
import xlwt
wb = xlrd.open_workbook(filename="D:/py/百度数据分析/百度合作单位-人员管理-二期.xls")
sheet=wb.sheet_by_index(0)
print(sheet.nrows)
print(sheet.ncols)
#统计人数
zongsu=0
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="序号":
        print("总人数为：",max(sheet.col_values(i)[1:]))
        zongsu=max(sheet.col_values(i)[1:])
#统计电话类别
acc = []
number_dx=0
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="电话号码":
        for x in sheet.col_values(i):
            x=str(x)
            if x.startswith("14") or x.startswith("17"):
                acc.append(x)
print("电信的用户为：", len(acc))
number_dx=len(acc)

number_yd=0
aca = []
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="电话号码":
        for y in sheet.col_values(i):
            y=str(y)
            if y.startswith("13"):
                aca.append(y)
print("移动的用户为：", len(aca))
number_yd=len(aca)

number_lt=0
acb = []
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="电话号码":
        for z in sheet.col_values(i):
            z=str(z)
            if z.startswith("15"):
                acb.append(z)
print("联通的用户为：", len(acb))
number_lt=len(acb)
#公司男女人数
man=0
woman=0
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="性别":
        print("男生的人数为:{},女生的人数为:{}".format(sheet.col_values(i).count("男"),sheet.col_values(i).count("女")))
        man=sheet.col_values(i).count("男")
        woman=sheet.col_values(i).count("女")
#老员工数
oldwork=0
age=[]
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="年龄":
        for x in sheet.col_values(i)[1:]:
            x=int(x)
            if x > 45:
                age.append(x)
print("老员工数：{}".format(len(age)))
oldwork=len(age)
#薪资分层
money=[]
money1=[]
piad=0
low=0
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="薪资":
        for x in sheet.col_values(i)[1:]:
            x=int(x)
            if x > 8000:
                money.append(x)
            elif x < 3000:
                money1.append(x)
print("高新人数：{}".format(len(money)),"低薪人数：{}".format(len(money1)))
piad=len(money)
low=len(money1)
#传媒员工
media1=[]
media=0
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="外包公司":
        for x in sheet.col_values(i):
            x=str(x)
            if '传媒'in x:
                media1.append(x)
print("去传媒的人数：{}".format(len(media1)))
media=len(media1)
region1=[]
region=0
for i in range(sheet.ncols):
    if sheet.col_values(i)[0]=="居住地址":
        for x in sheet.col_values(i):
            x=str(x)
            if x.startswith('黑龙江') or x.startswith('北京') or x.startswith('福建') or x.startswith('四川'):
                region1.append(x)
print("在疫情高危地区的人数：{}".format(len(region1)))
region=len(region1)
def insert(sql,par):
    conn = pymysql.connect(host="localhost",user="root",password="123456",database="baidu",charset='utf8')
    cur = conn.cursor()
    cur.execute(sql,par)
    conn.commit()
    cur.close()
    conn.close()
    print("存入数据库")
sql ="insert into xls value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param = [zongsu,number_dx,number_yd,number_lt,man,woman,oldwork,piad,low,media,region]
insert(sql,param)
